﻿using H.Dao.SQLite.Daos.Cache;
using H.Daos;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;
using System.Text;

namespace H.Dao.SQLite.Daos
{
    /// <summary>
    /// 基础查询对象
    /// </summary>
    public abstract class AbstractQuery : IAbstractQuery
    {
        /// <summary>
        /// 与查询对象关联的Dao对象
        /// </summary>
        protected readonly HDao _HDao;

        /// <summary>
        /// 当前准备查询的数据库表
        /// </summary>
        protected string _Table = null;

        /// <summary>
        /// 是否去除重复结果
        /// </summary>
        protected bool _Distinct = false;

        /// <summary>
        /// 当前所有需要从数据库获取的字段
        /// </summary>
        protected string[] _Fields = null;

        /// <summary>
        /// 排序顺序
        /// </summary>
        protected string _Order = null;

        /// <summary>
        /// 限制数据条数
        /// </summary>
        protected string _Limit = null;

        /// <summary>
        /// Where语句
        /// </summary>
        protected StringBuilder _Where = null;

        /// <summary>
        /// SQL语句中的参数
        /// </summary>
        protected Dictionary<string, object> _Params = null;

        /// <summary>
        /// SQL参数序号
        /// </summary>
        private int _ParamIdx = 0;

        /// <summary>
        /// 获取当前查询对象进行查询的数据库表
        /// </summary>
        public virtual string TableName => _Table;

        /// <summary>
        /// 获取当前需要进行展示的列名
        /// </summary>
        protected virtual string ColumnNames => _Fields != null && _Fields.Length > 0 ? string.Join(",", _Fields) : "*";

        /// <summary>
        /// 往SQL语句中插入参数
        /// </summary>
        /// <param name="value">插入的参数的值</param>
        /// <returns>返回新生成的参数的占位符</returns>
        protected string PushParam(object value)
        {
            string key = NextParamName();
            _Params.Add(key, value);
            return key;
        }

        /// <summary>
        /// 获取下一个占位符的名称,所有占位符都应当统一采用它进行生成,避免出现冲突
        /// </summary>
        /// <returns></returns>
        protected string NextParamName()
        {
            return $"@val_{_ParamIdx++}";
        }

        /// <summary>
        /// 检查是否Where语句StringBuilder和_Params是否就绪,如果没有就绪,则初始化它
        /// </summary>
        protected void PrepareWhere(bool or)
        {
            if (_Where == null)
            {
                _Where = new StringBuilder(" WHERE");
                _Params = new Dictionary<string, object>();
            }
            else
            {
                if (or)
                {
                    _Where.Append(" OR");
                }
                else
                {
                    _Where.Append(" AND");
                }
            }
        }


        /// <summary>
        /// 构建一个查询对象
        /// </summary>
        /// <param name="dao"></param>
        protected AbstractQuery(HDao dao)
        {
            _HDao = dao;
        }

        /// <summary>
        /// 克隆一个查询对象
        /// </summary>
        /// <param name="query"></param>
        protected AbstractQuery(AbstractQuery query)
        {
            _HDao = query._HDao;
            _Table = query.TableName;
            _Where = query._Where != null ? new StringBuilder(query._Where.ToString()) : null;
            _Params = query._Params != null ? new Dictionary<string, object>(query._Params) : null;
            _ParamIdx = query._ParamIdx;
            _Fields = query._Fields;
            _Order = query._Order;
            _Limit = query._Limit;
        }

        ///<inheritdoc/>
        public string FetchSelectSQL(out KeyValuePair<string, object>[] valueBinding)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append($"SELECT {(_Distinct ? "DISTINCT " : "")}{ColumnNames} FROM {TableName}");

            if (_Where != null && _Where.Length > 0)
            {
                //连接Where子句
                sb.Append(_Where.ToString());
            }

            if (_Order != null)
            {
                sb.Append(_Order);
            }

            if (_Limit != null)
            {
                sb.Append(_Limit);
            }

            sb.Append(";");

            if (_Params != null)
            {
                valueBinding = _Params.ToArray();
            }
            else
            {
                valueBinding = new KeyValuePair<string, object>[0];
            }
            return sb.ToString();
        }

        ///<inheritdoc/>
        public string FetchColumnSQL(string field, out KeyValuePair<string, object>[] valueBinding)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append($"SELECT DISTINCT {field} FROM {TableName}");

            if (_Where != null && _Where.Length > 0)
            {
                //连接Where子句
                sb.Append(_Where.ToString());
            }

            if (_Order != null)
            {
                sb.Append(_Order);
            }

            if (_Limit != null)
            {
                sb.Append(_Limit);
            }

            sb.Append(";");

            if (_Params != null)
            {
                valueBinding = _Params.ToArray();
            }
            else
            {
                valueBinding = new KeyValuePair<string, object>[0];
            }
            return sb.ToString();
        }

        ///<inheritdoc/>
        public string FetchValueSQL(string field, out KeyValuePair<string, object>[] valueBinding)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append($"SELECT {field} FROM {TableName}");

            if (_Where != null && _Where.Length > 0)
            {
                //连接Where子句
                sb.Append(_Where.ToString());
            }

            if (_Order != null)
            {
                sb.Append(_Order);
            }

            sb.Append(" LIMIT 1;");

            if (_Params != null)
            {
                valueBinding = _Params.ToArray();
            }
            else
            {
                valueBinding = new KeyValuePair<string, object>[0];
            }
            return sb.ToString();
        }

        ///<inheritdoc/>
        public string FetchUpdateSQL(KeyValuePair<string, object>[] values, out KeyValuePair<string, object>[] valueBinding)
        {
            if (values == null || values.Length == 0)
            {
                throw new ArgumentNullException("必须指定要更新的数据");
            }
            StringBuilder sb = new StringBuilder();
            sb.Append($"UPDATE {TableName} SET ");

            int i;
            if (_Params != null)
            {
                i = _Params.Count;
                valueBinding = new KeyValuePair<string, object>[_Params.Count + values.Length];
                Array.Copy(_Params.ToArray(), valueBinding, _Params.Count);
            }
            else
            {
                i = 0;
                valueBinding = new KeyValuePair<string, object>[values.Length];
            }

            bool isFirst = true;

            foreach (KeyValuePair<string, object> kv in values)
            {
                if (isFirst)
                {
                    isFirst = false;
                }
                else
                {
                    sb.Append(',');
                }
                string key = NextParamName();
                sb.Append($"{kv.Key}={key}");
                valueBinding[i] = new KeyValuePair<string, object>(key, kv.Value);
            }
            if (_Where != null && _Where.Length > 0)
            {
                //连接Where子句
                sb.Append(_Where.ToString());
            }
            sb.Append(';');

            return sb.ToString();
        }

        ///<inheritdoc/>
        public string FetchDeleteSQL(out KeyValuePair<string, object>[] valueBinding)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append($"DELETE FROM {TableName}");

            if (_Where != null && _Where.Length > 0)
            {
                //连接Where子句
                sb.Append(_Where.ToString());
            }

            sb.Append(';');

            if (_Params != null)
            {
                valueBinding = _Params.ToArray();
            }
            else
            {
                valueBinding = new KeyValuePair<string, object>[0];
            }

            return sb.ToString();
        }

        ///<inheritdoc/>
        public T[] Column<T>(string field)
        {
            string sql = FetchColumnSQL(field, out KeyValuePair<string, object>[] bindings);
            return _HDao.Column<T>(sql, bindings);
        }

        ///<inheritdoc/>
        public T Value<T>(string field)
        {
            string sql = FetchColumnSQL(field, out KeyValuePair<string, object>[] bindings);
            return _HDao.Value<T>(sql, bindings);
        }

        ///<inheritdoc/>
        public int Delete()
        {
            string sql = FetchDeleteSQL(out KeyValuePair<string, object>[] bindings);
            return _HDao.Execute(sql, bindings);
        }

        ///<inheritdoc/>
        public int Update(KeyValuePair<string, object>[] values)
        {
            string sql = FetchUpdateSQL(values, out KeyValuePair<string, object>[] bindings);
            return _HDao.Execute(sql, bindings);
        }

        ///<inheritdoc/>
        public int Update(string column, object value)
        {
            string sql = FetchUpdateSQL(
                new KeyValuePair<string, object>[] {
                new KeyValuePair<string, object>(column, value)
            }, out KeyValuePair<string, object>[] bindings);
            return _HDao.Execute(sql, bindings);
        }
    }


    public class HQuery : AbstractQuery, IQuery
    {

        ///<inheritdoc/>
        public HQuery(HDao dao) : base(dao)
        {

        }

        ///<inheritdoc/>
        public HQuery(AbstractQuery query) : base(query)
        {

        }

        ///<inheritdoc/>
        public IQuery Table(string tableName)
        {
            _Table = tableName;
            _Distinct = false;
            return this;
        }

        ///<inheritdoc/>
        public IQuery Distinct(params string[] fields)
        {
            _Fields = fields;
            _Distinct = true;
            return this;
        }

        ///<inheritdoc/>
        public IQuery Fields(params string[] fields)
        {
            _Fields = fields;
            return this;
        }

        ///<inheritdoc/>
        public IQuery Limit(int limit)
        {
            _Limit = limit > 0 ? $" LIMIT {limit}" : null;
            return this;
        }

        ///<inheritdoc/>
        public IQuery Limit(int limit, int offset)
        {
            _Limit = limit > 0 && offset >= 0 ? $" LIMIT {limit} OFFSET {Math.Max(0, offset)}" : null;
            return this;
        }

        ///<inheritdoc/>
        public IQuery Order(string field, bool desc = false)
        {
            if (field != null)
            {
                _Order = $" ORDER BY {field} {(desc ? "DESC" : "ASC")}";
            }
            else
            {
                _Order = null;
            }
            return this;
        }

        ///<inheritdoc/>
        public IQuery Order(string[] fields, bool desc = false)
        {
            if (fields != null)
            {
                _Order = $" ORDER BY {string.Join(",", fields)} {(desc ? "DESC" : "ASC")}";
            }
            else
            {
                _Order = null;
            }
            return this;
        }

        ///<inheritdoc/>
        public NameValueCollection[] Select()
        {
            string sql = FetchSelectSQL(out KeyValuePair<string, object>[] bindings);
            return _HDao.Select(sql, bindings);
        }

        ///<inheritdoc/>
        public IQuery Where(string field, string op, object value)
        {
            PrepareWhere(false);
            if (string.Compare(op, "in", true) == 0)
            {
                if (value is string)
                {
                    _Where.Append($" {field} {op} ({value})");
                }
                else if (value.GetType().IsArray)
                {
                    _Where.Append($" {field} {op} ({string.Join(",", (object[])value)}");
                }
            }
            else
            {
                _Where.Append($" {field} {op} {PushParam(value)}");
            }
            return this;
        }

        ///<inheritdoc/>
        public IQuery WhereOr(string field, string op, object value)
        {
            PrepareWhere(true);
            if (string.Compare(op, "in", true) == 0)
            {
                if (value is string)
                {
                    _Where.Append($" {field} {op} ({value})");
                }
                else if (value.GetType().IsArray)
                {
                    _Where.Append($" {field} {op} ({string.Join(",", (object[])value)}");
                }
            }
            else
            {
                _Where.Append($" {field} {op} {PushParam(value)}");
            }
            return this;
        }

        ///<inheritdoc/>
        public IQuery WhereNotNull(string field)
        {
            PrepareWhere(false);
            _Where.Append($" {field} IS NOT NULL");
            return this;
        }

        ///<inheritdoc/>
        public IQuery WhereNotNullOr(string field)
        {
            PrepareWhere(true);
            _Where.Append($" {field} IS NOT NULL");
            return this;
        }

        ///<inheritdoc/>
        public IQuery WhereNull(string field)
        {
            PrepareWhere(false);
            _Where.Append($" {field} IS NULL");
            return this;
        }

        ///<inheritdoc/>
        public IQuery WhereNullOr(string field)
        {
            PrepareWhere(true);
            _Where.Append($" {field} IS NULL");
            return this;
        }


        ///<inheritdoc/>
        public IQuery Clone()
        {
            return new HQuery(this);
        }
    }


    public class HQuery<M> : AbstractQuery, IQuery<M> where M : AbstractEntity, new()
    {
        /// <summary>
        /// 重写获取表名方法
        /// </summary>
        public override string TableName => _Table ?? ModelMapping<M>.TableName;

        /// <summary>
        /// 获取所有需要显示的列名
        /// </summary>
        protected override string ColumnNames => _Fields != null && _Fields.Length > 0 ? string.Join(",", _Fields) : ModelMapping<M>.ColumnNames;


        ///<inheritdoc/>
        public HQuery(HDao dao) : base(dao) { }

        ///<inheritdoc/>
        public HQuery(AbstractQuery query) : base(query) { }

        ///<inheritdoc/>
        public IQuery<M> Table(string tableName)
        {
            _Table = tableName;
            return this;
        }

        ///<inheritdoc/>
        public IQuery Fields(params string[] fields)
        {
            return new HQuery(this).Fields(fields);
        }

        ///<inheritdoc/>
        public IQuery Distinct(params string[] fields)
        {
            return new HQuery(this).Distinct(fields);
        }

        ///<inheritdoc/>
        public IQuery<M> Limit(int limit)
        {
            _Limit = limit > 0 ? $" LIMIT {limit}" : null;
            return this;
        }

        ///<inheritdoc/>
        public IQuery<M> Limit(int limit, int offset)
        {
            _Limit = limit > 0 && offset >= 0 ? $" LIMIT {limit} OFFSET {Math.Max(0, offset)}" : null;
            return this;
        }

        ///<inheritdoc/>
        public IQuery<M> Order(string field, bool desc = false)
        {
            if (field != null)
            {
                _Order = $" ORDER BY {field} {(desc ? "DESC" : "ASC")}";
            }
            else
            {
                _Order = null;
            }
            return this;
        }

        ///<inheritdoc/>
        public IQuery<M> Order(string[] fields, bool desc = false)
        {
            if (fields != null)
            {
                _Order = $" ORDER BY {string.Join(",", fields)} {(desc ? "DESC" : "ASC")}";
            }
            else
            {
                _Order = null;
            }
            return this;
        }

        ///<inheritdoc/>
        public M[] Select()
        {
            string sql = FetchSelectSQL(out KeyValuePair<string, object>[] bindings);
            return _HDao.Select<M>(sql, bindings);
        }

        ///<inheritdoc/>
        public IQuery<M> Where(string field, string op, object value)
        {
            PrepareWhere(false);
            if (string.Compare(op, "in", true) == 0)
            {
                if (value is string)
                {
                    _Where.Append($" {field} {op} ({value})");
                }
                else if (value.GetType().IsArray)
                {
                    _Where.Append($" {field} {op} ({string.Join(",", (object[])value)}");
                }
            }
            else
            {
                _Where.Append($" {field} {op} {PushParam(value)}");
            }
            return this;
        }

        ///<inheritdoc/>
        public IQuery<M> WhereOr(string field, string op, object value)
        {
            PrepareWhere(true);
            if (string.Compare(op, "in", true) == 0)
            {
                if (value is string)
                {
                    _Where.Append($" {field} {op} ({value})");
                }
                else if (value.GetType().IsArray)
                {
                    _Where.Append($" {field} {op} ({string.Join(",", (object[])value)}");
                }
            }
            else
            {
                _Where.Append($" {field} {op} {PushParam(value)}");
            }
            return this;
        }

        ///<inheritdoc/>
        public IQuery<M> WhereNotNull(string field)
        {
            PrepareWhere(false);
            _Where.Append($" {field} IS NOT NULL");
            return this;
        }

        ///<inheritdoc/>
        public IQuery<M> WhereNotNullOr(string field)
        {
            PrepareWhere(true);
            _Where.Append($" {field} IS NOT NULL");
            return this;
        }

        ///<inheritdoc/>
        public IQuery<M> WhereNull(string field)
        {
            PrepareWhere(false);
            _Where.Append($" {field} IS NULL");
            return this;
        }

        ///<inheritdoc/>
        public IQuery<M> WhereNullOr(string field)
        {
            PrepareWhere(true);
            _Where.Append($" {field} IS NULL");
            return this;
        }

        ///<inheritdoc/>
        public IQuery<M> Clone()
        {
            return new HQuery<M>(this);
        }
    }
}
